#!/bin/bash
# 作用：ADS层，每日数据装载（DWS -> ADS）

# 1.判断参数
if [ $# -lt 1 ]
then
	echo "请输入正确参数表名/all" && exit
fi

# 2.定时时间属性
[ "$2" ] && dateStr=$2 || dateStr=`date -d '-1 day' +%F`

# 3.执行导数SQL
ads_coupon_stats="insert overwrite table ads_coupon_stats
select * from ads_coupon_stats where dt!='${dateStr}'
union all
select '${dateStr}' dt,coupon_id,coupon_name,
       sum(used_count_1d),
       count(distinct user_id)
from dws_tool_user_coupon_coupon_used_1d
where dt='${dateStr}'
group by coupon_id,coupon_name;"
ads_new_order_user_stats="insert overwrite table ads_new_order_user_stats
select * from ads_new_order_user_stats where dt!='${dateStr}'
union all
select '${dateStr}' dt,recent_days,count(user_id)
from (
         select user_id, order_date_first
         from dws_trade_user_order_td
         where dt = '${dateStr}'
     ) t1
lateral view explode(array(1,7,30)) tmp as recent_days
where order_date_first<='${dateStr}' and order_date_first>=date_sub('${dateStr}',recent_days - 1)
group by recent_days;"
ads_order_by_province="insert overwrite table ads_order_by_province
select * from ads_order_by_province where dt!='${dateStr}'
union all
select '${dateStr}' dt,
       recent_days,
       province_id,
       province_name,
       area_code,
       iso_code,
       iso_3166_2,
       order_count_nd,
       order_total_amount_nd
from dws_trade_province_order_nd
where dt='${dateStr}';"
ads_order_continuously_user_count="insert overwrite table ads_order_continuously_user_count
select * from ads_order_continuously_user_count where dt!='${dateStr}'
union all
select '${dateStr}' dt,7 recent_days,count(distinct user_id)
from (
         select user_id, count(1)
         from (
                  select user_id,
                         dt,
                         date_sub(dt, rn) flag
                  from (
                           select user_id,
                                  dt,
                                  row_number() over (partition by user_id order by dt) rn
                           from dws_trade_user_order_1d
                           where dt <= '${dateStr}'
                             and dt >= date_sub('${dateStr}', 6)
                       ) t1
              ) t2
         group by user_id, flag
         having count(1) >= 3
     ) t3;"
ads_order_stats_by_cate="insert overwrite table ads_order_stats_by_cate
select * from ads_order_stats_by_cate where dt!='${dateStr}'
union all
select '${dateStr}' dt,recent_days,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,
       sum(order_count_nd),
       count(distinct user_id)
from dws_trade_user_sku_order_nd
where dt='${dateStr}'
group by recent_days,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name;"
ads_order_stats_by_tm="insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm where dt != '${dateStr}'
union all
select '${dateStr}' dt,
       recent_days,
       tm_id,
       tm_name,
       sum(order_count_nd),
       count(distinct user_id)
from dws_trade_user_sku_order_nd
where dt='${dateStr}'
group by recent_days,tm_id,tm_name;"
ads_order_to_pay_interval_avg="insert overwrite table ads_order_to_pay_interval_avg
select * from ads_order_to_pay_interval_avg where dt!='${dateStr}'
union all
select '${dateStr}' dt,
       avg(to_unix_timestamp(payment_time) - to_unix_timestamp(order_time))
from dwd_trade_trade_flow_acc
where dt in ('9999-99-99','${dateStr}') and payment_date_id='${dateStr}';"
ads_page_path="insert overwrite table ads_page_path
select * from ads_page_path where dt!='${dateStr}'
union all
select '${dateStr}' dt,
       source,
       target,
       count(1)
from (
         select page_id source, lead(page_id, 1, 'null') over (partition by session_id order by view_time) target
         from dwd_traffic_page_view_inc
         where dt = '${dateStr}'
     ) t1
group by source,target;"
ads_repeat_purchase_by_tm="insert overwrite table ads_repeat_purchase_by_tm
select * from ads_repeat_purchase_by_tm where dt!='${dateStr}'
union all
select '${dateStr}' dt,
       30 recent_days,
       tm_id,
       tm_name,
       sum(if(order_count_nd > 1,1,0)) / count(*)
from dws_trade_user_sku_order_nd
where dt='${dateStr}' and recent_days=30
group by tm_id,tm_name;"
ads_sku_cart_num_top3_by_cate="with
cate as (
    select id,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name
    from dim_sku_full
    where dt='${dateStr}'
)
insert overwrite table ads_sku_cart_num_top3_by_cate
select * from ads_sku_cart_num_top3_by_cate where dt!='${dateStr}'
union all
select '${dateStr}',
       category1_id,
       category1_name,
       category2_id,
       category2_name,
       category3_id,
       category3_name,
       sku_id,
       sku_name,
       sku_num,
       rn
from (
     select cate.category1_id,
       cate.category1_name,
       cate.category2_id,
       cate.category2_name,
       cate.category3_id,
       cate.category3_name,
       tc.sku_id,
       tc.sku_name,
       tc.sku_num,
       row_number() over (partition by category3_id order by tc.sku_num desc ) rn
    from dwd_trade_cart_full tc
    join cate on tc.sku_id=cate.id
)t1
where rn<=3;"
ads_sku_favor_count_top3_by_tm="insert overwrite table ads_sku_favor_count_top3_by_tm
select * from ads_sku_favor_count_top3_by_tm where dt!='${dateStr}'
union all
select '${dateStr}',tm_id, tm_name, sku_id, sku_name, favor_add_count_1d, rn
from (
         select tm_id,
                tm_name,
                sku_id,
                sku_name,
                favor_add_count_1d,
                row_number() over (partition by tm_id order by favor_add_count_1d desc) rn
         from dws_interaction_sku_favor_add_1d
         where dt = '${dateStr}'
     ) t1;"
ads_traffic_stats_by_channel="insert overwrite table ads_traffic_stats_by_channel
select * from ads_traffic_stats_by_channel where dt!='${dateStr}'
union all
select '${dateStr}' dt,recent_days,channel,
       count(distinct mid_id),
       avg(during_time_1d) / 1000,
       avg(page_count_1d),
       count(1),
       sum(if(page_count_1d=1,1,0)) / count(1)
from dws_traffic_session_page_view_1d
lateral view explode(array(1,7,30)) tmp as recent_days
where dt <='${dateStr}' and dt>=date_sub('${dateStr}',recent_days - 1)
group by recent_days,channel;"
ads_user_action="with
pv as (
    select sum(if(page_id='home',1,0)) home_count,
           sum(if(page_id='good_detail',1,0)) good_detail_count
    from (
         select distinct user_id,page_id
         from dwd_traffic_page_view_inc
         where dt='${dateStr}' and (page_id='home' or page_id='good_detail')
    ) t1
),cart as (
    select count(1) cart_count
    from dws_trade_user_cart_add_1d
    where dt='${dateStr}'
),od as (
    select count(1) order_count
    from dws_trade_user_order_1d
    where dt='${dateStr}'
),pay as (
    select count(1) pay_count
    from dws_trade_user_payment_1d
    where dt='${dateStr}'
)
insert overwrite table ads_user_action
select * from ads_user_action where dt!='${dateStr}'
union all
select '${dateStr}',
       home_count,
       good_detail_count,
       cart_count,
       order_count,
       pay_count
from pv
join cart on true
join od on true
join pay on true;"
ads_user_change="insert overwrite table ads_user_change
select * from ads_user_change where dt!='${dateStr}'
union all
select '${dateStr}' dt,sum(falg1),sum(flag2)
from (
         select if(datediff('${dateStr}', last_login) = 7, 1, 0) falg1,
                if(last_login = '${dateStr}' and datediff('${dateStr}', last2_login) = 8, 1, 0) flag2
         from (
                  select max(login_date_last) last_login,
                         min(login_date_last) last2_login
                  from dws_user_user_login_td
                  where dt <= '${dateStr}' and dt >= date_sub('${dateStr}', 1)
                  group by user_id
              ) t1
     ) t2;"
ads_user_retention="with
register as (
    select dt,user_id
    from dwd_user_register_inc
    where dt<'${dateStr}' and dt>=date_sub('${dateStr}',7)
),login as (
    select user_id,login_date_last
    from dws_user_user_login_td
    where dt='${dateStr}'
)
insert overwrite table ads_user_retention
select * from ads_user_retention where dt!='${dateStr}'
union all
select '${dateStr}' dt,
       dt create_date,
       datediff('${dateStr}',dt),
       sum(if(login_date_last='${dateStr}',1,0)),
       count(distinct register.user_id),
       sum(if(login_date_last='${dateStr}',1,0)) / count(distinct register.user_id)
from register join login on register.user_id=login.user_id
group by dt;"
ads_user_stats="with
new as (
    select recent_days,count(distinct user_id) new_count
    from dwd_user_register_inc
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt<='${dateStr}' and dt>=date_sub('${dateStr}',recent_days - 1)
    group by recent_days
),active as (
    select recent_days,count(distinct user_id) active_count
    from dwd_user_login_inc
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt<='${dateStr}' and dt>=date_sub('${dateStr}',recent_days - 1)
    group by recent_days
)
insert overwrite table ads_user_stats
select * from ads_user_stats where dt!='${dateStr}'
union all
select '${dateStr}' dt,
       new.recent_days,
       new.new_count,
       active.active_count
from new
join active on new.recent_days=active.recent_days;"

case $1 in
"all")
	$HIVE_HOME/bin/hive -e "${ads_coupon_stats}${ads_new_order_user_stats}${ads_order_by_province}${ads_order_continuously_user_count}${ads_order_stats_by_cate}${ads_order_stats_by_tm}${ads_order_to_pay_interval_avg}${ads_page_path}${ads_repeat_purchase_by_tm}${ads_sku_cart_num_top3_by_cate}${ads_sku_favor_count_top3_by_tm}${ads_traffic_stats_by_channel}${ads_user_action}${ads_user_change}${ads_user_retention}${ads_user_stats}"
;;
"ads_coupon_stats")
    $HIVE_HOME/bin/hive -e "${ads_coupon_stats}"
;;
"ads_new_order_user_stats")
    $HIVE_HOME/bin/hive -e "${ads_new_order_user_stats}"
;;
"ads_order_by_province")
    $HIVE_HOME/bin/hive -e "${ads_order_by_province}"
;;
"ads_order_continuously_user_count")
    $HIVE_HOME/bin/hive -e "${ads_order_continuously_user_count}"
;;
"ads_order_stats_by_cate")
    $HIVE_HOME/bin/hive -e "${ads_order_stats_by_cate}"
;;
"ads_order_stats_by_tm")
    $HIVE_HOME/bin/hive -e "${ads_order_stats_by_tm}"
;;
"ads_order_to_pay_interval_avg")
    $HIVE_HOME/bin/hive -e "${ads_order_to_pay_interval_avg}"
;;
"ads_page_path")
    $HIVE_HOME/bin/hive -e "${ads_page_path}"
;;
"ads_repeat_purchase_by_tm")
    $HIVE_HOME/bin/hive -e "${ads_repeat_purchase_by_tm}"
;;
"ads_sku_cart_num_top3_by_cate")
    $HIVE_HOME/bin/hive -e "${ads_sku_cart_num_top3_by_cate}"
;;
"ads_sku_favor_count_top3_by_tm")
    $HIVE_HOME/bin/hive -e "${ads_sku_favor_count_top3_by_tm}"
;;
"ads_traffic_stats_by_channel")
    $HIVE_HOME/bin/hive -e "${ads_traffic_stats_by_channel}"
;;
"ads_user_action")
    $HIVE_HOME/bin/hive -e "${ads_user_action}"
;;
"ads_user_change")
    $HIVE_HOME/bin/hive -e "${ads_user_change}"
;;
"ads_user_retention")
    $HIVE_HOME/bin/hive -e "${ads_user_retention}"
;;
"ads_user_stats")
    $HIVE_HOME/bin/hive -e "${ads_user_stats}"
;;
esac
